package com.sf.dlb.mapper.dao;

import com.sf.dlb.jpa.model.UserWarningInfo;
import com.sf.dlb.vo.WalletListVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.mapstruct.Mapper;

import java.util.List;
import java.util.Map;


/**
 * 用户预警 Repository
 *
 * Created by jing.tan on 2018/5/21.
 */
@Mapper
public interface WarningMapper {
    /**
     * 查询用户信息-认证，钱包
     * @param userIds
     * @return
     */
    @Select("<script>" +
            "select a.user_id,IFNULL(r.state,'1') as 'state',(IFNULL(w.lock_dlb,0)+IFNULL(w.unlock_dlb,0)+IFNULL(w.available_dlb,0)) as 'wallet_count' from user_info a LEFT JOIN user_real_info r on a.user_id=r.user_id LEFT JOIN user_wallet w on a.user_id=w.user_id where a.user_id in " +
            "   <foreach item='item' index='index' collection='userIds' open='(' separator=',' close=')'>" +
            "       #{item}  " +
            "   </foreach>" +
            "</script>")
    List<Map> findbyUserInfo(@Param("userIds") List<String> userIds);

    /**
     * 预警因子1-凡是某天累计阅读时间超过3个小时；
     * @param date
     * @return
     */
    @Select("select n.user_id,sum(n.read_time_long) as 'read_long' from new_recod n where DATE_FORMAT(n.create_date,'%Y-%m-%d')=#{date} AND n.get_state=#{getState} group by n.user_id HAVING read_long>=#{maxReadLong}")
    List<Map> warningReadTime(@Param("date") String date,@Param("getState") String getState,@Param("maxReadLong") Integer maxReadLong);
    /**
     * 预警因子2-凡是某天累计阅读数量超过300篇；
     * @param date
     * @return
     */
    @Select("select n.user_id,count(*) as 'read_number' from new_recod n where DATE_FORMAT(n.create_date,'%Y-%m-%d')=#{date} group by n.user_id HAVING read_number>=#{maxReadNumber}")
    List<Map> warningReadNumber(@Param("date") String date,@Param("maxReadNumber") Integer maxReadNumber);
    /**
     * 预警因子3-注册30天之后没有阅读的
     *  冻结的用户不要再预警了
     * @return
     */
    @Select("SELECT a.*,n.user_id AS 'user_id2' FROM user_info a LEFT JOIN new_recod n on a.user_id=n.user_id WHERE TO_DAYS(now()) - TO_DAYS(a.create_date) >= #{day} and (n.user_id is null or n.user_id='') and a.login_flag='0'")
    List<Map> warningRegisteredNoRead(@Param("day")int day);
    /**
     * 查询用户某天预警信息
     * @param userId
     * @return
     */
    @Select("select * from user_warning a where a.user_id=#{userId} and DATE_FORMAT(a.create_date,'%Y-%m-%d')=#{date}")
    UserWarningInfo queryUserIdAndDate(@Param("userId") String userId, @Param("date") String date);
    /**
     * 查询类型余额-从dlb流水里查
    * @param userId
     * @return
     */
    @Select("SELECT sum(a.dlb_number) as 'dlb_number' FROM user_dlb_record a where a.user_id=#{userId} and a.dlb_type=#{dlbType}")
    Double queryUserDlbRecordAndDlbType(@Param("userId") String userId, @Param("dlbType") String dlbType);
    /**
     * 查询来源余额-从dlb流水里查
     * @param userId
     * @return
     */
    @Select("SELECT sum(a.dlb_number) AS 'dlb_number' FROM user_dlb_record a WHERE a.user_id =#{userId} and a.source=#{source}")
    Double queryUserDlbRecordAndSource(@Param("userId") String userId, @Param("source") String source);
    /**
     * 查询预警列表
     * @return
     */
    @Select("select a.user_id,SUBSTRING_INDEX(group_concat(a.warning_why order by a.create_date desc),\",\",1) as 'warning_why',count(*) as 'warning_count',(TO_DAYS(now())-TO_DAYS(SUBSTRING_INDEX(group_concat(a.create_date order by a.create_date),\",\",1))) as 'no_handler_day',SUBSTRING_INDEX(group_concat(a.create_date order by a.create_date desc),\",\",1) as 'create_date' from user_warning a where a.dealt_state='1' group by a.user_id")
    List<Map> findByWarningAll();
    /**
     * 查询预警列表
     * @return
     */
    @Select("select a.user_id,SUBSTRING_INDEX(group_concat(a.warning_why order by a.create_date desc),\",\",1) as 'warning_why',count(*) as 'warning_count',(TO_DAYS(now())-TO_DAYS(SUBSTRING_INDEX(group_concat(a.create_date order by a.create_date),\",\",1))) as 'no_handler_day',SUBSTRING_INDEX(group_concat(a.create_date order by a.create_date desc),\",\",1) as 'create_date' from user_warning a where a.dealt_state='1' group by a.user_id")
    List<WalletListVO> findByWarningDetails();
}